package com.asiainfo_linkage.service;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.nutz.dao.Dao;
import org.nutz.dao.Sqls;
import org.nutz.dao.sql.Sql;
import org.nutz.dao.sql.SqlCallback;
import org.nutz.ioc.loader.annotation.Inject;
import org.nutz.ioc.loader.annotation.IocBean;
import org.nutz.service.EntityService;

import com.asiainfo_linkage.model.TaskQuery;

@IocBean
public class RemQueryService extends EntityService<TaskQuery>{
	
	@Inject
	protected Dao dao;
	
	@SuppressWarnings({ "rawtypes", "unchecked" })
	public List<Map> pageQuerySelf(TaskQuery taskQuery) {
		String sqlstring="SELECT  distinct task_name,REM_TASK.TASK_ID task_id,rem_way,rem_type,rem_status,cycle_type,rem_first_time,rem_start_date,rem_end_date " +
				"FROM REM_GROUP,REM_MEMBER ,REM_TASK,REM_TASKMEMBER,REM_PLAN " +
				"WHERE REM_GROUP.group_id=REM_MEMBER.group_id  AND REM_MEMBER.member_id=REM_TASKMEMBER.member_id " +
				"AND  REM_TASK.TASK_ID=REM_TASKMEMBER.TASK_ID(+) " +
				"and REM_TASK.task_id=REM_PLAN.task_id(+)";
		
		List  listParam=new ArrayList ();
	if(StringUtils.isNotBlank(taskQuery.getGroup_name())){
		sqlstring+=" and group_name=@group_name";	
		Map map=new HashMap();
			map.put("group_name", taskQuery.getGroup_name());
			listParam.add(map);
	    }
		if(StringUtils.isNotBlank(taskQuery.getTask_name())){
			sqlstring+=" and task_name=@task_name";	
			Map map=new HashMap();
			map.put("task_name", taskQuery.getTask_name());
			listParam.add(map);
	    }
		
		if(StringUtils.isNotBlank(taskQuery.getMember_name())){
			sqlstring+=" and member_name like @member_name";	
			Map map=new HashMap();
			map.put("member_name", taskQuery.getMember_name());
			listParam.add(map);
	    }
		if(StringUtils.isNotBlank(taskQuery.getRem_way())){
				sqlstring+=" and rem_way=@rem_way";	
				Map map=new HashMap();
				map.put("rem_way", taskQuery.getRem_way());
				listParam.add(map);
			
	    } 
		if(StringUtils.isNotBlank(taskQuery.getRem_type())){
			sqlstring+=" and rem_type=@rem_type";	
			Map map=new HashMap();
			map.put("rem_type", taskQuery.getRem_type());
			listParam.add(map);
	    } 
		if(StringUtils.isNotBlank(taskQuery.getRem_status())){
			sqlstring+=" and rem_status=@rem_status";	
			Map map=new HashMap();
			map.put("rem_status", taskQuery.getRem_status());
			listParam.add(map);
	    } 
		if(StringUtils.isNotBlank(taskQuery.getCycle_type())){
			sqlstring+=" and cycle_type=@cycle_type";	
			Map map=new HashMap();
			map.put("cycle_type", taskQuery.getCycle_type());
			listParam.add(map);
	    } 
		
		
		if(taskQuery.getRem_first_time()!=null){
			sqlstring+=" and rem_first_time=@rem_first_time";	
			Map map=new HashMap();
			map.put("rem_first_time", taskQuery.getRem_first_time());
			listParam.add(map);
	    }

		if(taskQuery.getRem_start_date()!=null){
			sqlstring+=" and rem_start_date>=@rem_start_date";	
			Map map=new HashMap();
			map.put("rem_start_date", taskQuery.getRem_start_date());
			listParam.add(map);
	   }
		if(taskQuery.getRem_end_date()!=null){
			sqlstring+=" and rem_end_date<=@rem_end_date";	
			Map map=new HashMap();
			map.put("rem_end_date", taskQuery.getRem_end_date());
			listParam.add(map);
	    } 
		
        Sql sql = Sqls.create(sqlstring);
        
        for(int i=0;i<listParam.size();i++){
        	Map map=(Map) listParam.get(i);
        	Iterator it= map.keySet().iterator();
        	while(it.hasNext()){
        		String key=(String)it.next();
        		sql.params().set( key, map.get(key) );
        	}
        }
        final SimpleDateFormat sf=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        
        sql.setCallback(new SqlCallback() {
				public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
                        List<Map> list = new ArrayList<Map>();
                        Map tempMap=null;
                        while (rs.next()){
                        	tempMap=new HashMap();
                 //       	tempMap.put("group_name", rs.getString("group_name"));
                        	tempMap.put("task_name", rs.getString("task_name"));
                        	tempMap.put("task_id", rs.getString("task_id"));
                 //       	tempMap.put("member_name", rs.getString("member_name"));
                        	tempMap.put("rem_way", rs.getString("rem_way"));
                        	tempMap.put("rem_type", rs.getString("rem_type"));
                        	tempMap.put("rem_status", rs.getString("rem_status"));
                        	tempMap.put("cycle_type", rs.getString("cycle_type")); 
                        	try {
								tempMap.put("rem_first_time", sf.format(sf.parse(rs.getString("rem_first_time"))));
								if(StringUtils.isBlank(rs.getString("rem_start_date"))){
									tempMap.put("rem_start_date", "-");
								}else{
									tempMap.put("rem_start_date", sf.format(sf.parse(rs.getString("rem_start_date"))));
								}
							 	if(StringUtils.isBlank(rs.getString("rem_end_date"))){
							 		tempMap.put("rem_end_date", "-");
							 	}else{
							 		tempMap.put("rem_end_date", sf.format(sf.parse(rs.getString("rem_end_date"))));
							 	}
	                        	
							} catch (ParseException e) {
								e.printStackTrace();
							}
                        	list.add(tempMap);
                        }
                        return list;
                }
        });
        this.dao().execute(sql);
        return sql.getList(Map.class);    
}
	
	@SuppressWarnings({ "rawtypes", "unchecked" })
	public List<Map> pageQueryDetail(TaskQuery taskQuery) {
		String sqlstring="SELECT  distinct group_name,member_name,task_name,rem_way,rem_type,rem_status,cycle_type,rem_first_time,rem_start_date,rem_end_date " +
				"FROM REM_GROUP,REM_MEMBER ,REM_TASK,REM_TASKMEMBER,REM_PLAN " +
				"WHERE REM_GROUP.group_id=REM_MEMBER.group_id  AND REM_MEMBER.member_id=REM_TASKMEMBER.member_id " +
				"AND  REM_TASK.TASK_ID=REM_TASKMEMBER.TASK_ID(+) " +
				"and REM_TASK.task_id=REM_PLAN.task_id(+)"+
				"AND REM_TASK.task_id="+taskQuery.getTask_id();
		
		List  listParam=new ArrayList ();
if(StringUtils.isNotBlank(taskQuery.getGroup_name())){
		sqlstring+=" and group_name=@group_name";	
		Map map=new HashMap();
		map.put("group_name", taskQuery.getGroup_name());
		listParam.add(map);
	    }
		if(StringUtils.isNotBlank(taskQuery.getTask_name())){
			sqlstring+=" and task_name=@task_name";	
			Map map=new HashMap();
			map.put("task_name", taskQuery.getTask_name());
			listParam.add(map);
	    }
		
	if(StringUtils.isNotBlank(taskQuery.getMember_name())){
			sqlstring+=" and member_name=@member_name";	
			Map map=new HashMap();
			map.put("member_name", taskQuery.getMember_name());
			listParam.add(map);
	    }
		if(StringUtils.isNotBlank(taskQuery.getRem_way())){
				sqlstring+=" and rem_way=@rem_way";	
				Map map=new HashMap();
				map.put("rem_way", taskQuery.getRem_way());
				listParam.add(map);
			
	    } 
		if(StringUtils.isNotBlank(taskQuery.getRem_type())){
			sqlstring+=" and rem_type=@rem_type";	
			Map map=new HashMap();
			map.put("rem_type", taskQuery.getRem_type());
			listParam.add(map);
	    } 
		if(StringUtils.isNotBlank(taskQuery.getRem_status())){
			sqlstring+=" and rem_status=@rem_status";	
			Map map=new HashMap();
			map.put("rem_status", taskQuery.getRem_status());
			listParam.add(map);
	    } 
		if(StringUtils.isNotBlank(taskQuery.getCycle_type())){
			sqlstring+=" and cycle_type=@cycle_type";	
			Map map=new HashMap();
			map.put("cycle_type", taskQuery.getCycle_type());
			listParam.add(map);
	    } 
		
		
		if(taskQuery.getRem_first_time()!=null){
			sqlstring+=" and rem_first_time=@rem_first_time";	
			Map map=new HashMap();
			map.put("rem_first_time", taskQuery.getRem_first_time());
			listParam.add(map);
	    }

		if(taskQuery.getRem_start_date()!=null){
			sqlstring+=" and rem_start_date>=@rem_start_date";	
			Map map=new HashMap();
			map.put("rem_start_date", taskQuery.getRem_start_date());
			listParam.add(map);
	   }
		if(taskQuery.getRem_end_date()!=null){
			sqlstring+=" and rem_end_date<=@rem_end_date";	
			Map map=new HashMap();
			map.put("rem_end_date", taskQuery.getRem_end_date());
			listParam.add(map);
	    } 
		
        Sql sql = Sqls.create(sqlstring);
        
        for(int i=0;i<listParam.size();i++){
        	Map map=(Map) listParam.get(i);
        	Iterator it= map.keySet().iterator();
        	while(it.hasNext()){
        		String key=(String)it.next();
        		sql.params().set( key, map.get(key) );
        	}
        }
        final SimpleDateFormat sf=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        
        sql.setCallback(new SqlCallback() {
				public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
                        List<Map> list = new ArrayList<Map>();
                        Map tempMap=null;
                        while (rs.next()){
                        	tempMap=new HashMap();
                        	tempMap.put("group_name", rs.getString("group_name"));
                        	tempMap.put("task_name", rs.getString("task_name"));
                         	tempMap.put("member_name", rs.getString("member_name"));
                        	tempMap.put("rem_way", rs.getString("rem_way"));
                        	tempMap.put("rem_type", rs.getString("rem_type"));
                        	tempMap.put("rem_status", rs.getString("rem_status"));
                        	tempMap.put("cycle_type", rs.getString("cycle_type")); 
                        	try {
								tempMap.put("rem_first_time", sf.format(sf.parse(rs.getString("rem_first_time"))));
								if(StringUtils.isBlank(rs.getString("rem_start_date"))){
									tempMap.put("rem_start_date", "-");
								}else{
									tempMap.put("rem_start_date", sf.format(sf.parse(rs.getString("rem_start_date"))));
								}
							 	if(StringUtils.isBlank(rs.getString("rem_end_date"))){
							 		tempMap.put("rem_end_date", "-");
							 	}else{
							 		tempMap.put("rem_end_date", sf.format(sf.parse(rs.getString("rem_end_date"))));
							 	}
	                        	
							} catch (ParseException e) {
								e.printStackTrace();
							}
                        	list.add(tempMap);
                        }
                        return list;
                }
        });
        this.dao().execute(sql);
        return sql.getList(Map.class);    
}	

}
